USE [CompreConstrua]
GO

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Clientes
	DROP CONSTRAINT FK_Clientes_Cidades
GO
ALTER TABLE dbo.Cidades SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Clientes
	DROP CONSTRAINT DF_Clientes_recebeemails
GO
ALTER TABLE dbo.Clientes
	DROP CONSTRAINT DF_Clientes_ehconfirmado
GO
CREATE TABLE dbo.Tmp_Clientes
	(
	clienteid int NOT NULL IDENTITY (1, 1),
	nome varchar(50) NULL,
	login varchar(20) NULL,
	senha varchar(50) NULL,
	email varchar(100) NULL,
	endereco varchar(250) NULL,
	bairro varchar(150) NULL,
	cidadeid int NULL,
	estadoid int NULL,
	cep char(8) NULL,
	numero int NULL,
	complemento varchar(100) NULL,
	tel char(10) NULL,
	cel char(10) NULL,
	recebeemails bit NOT NULL,
	ehconfirmado bit NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Clientes SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_Clientes ADD CONSTRAINT
	DF_Clientes_recebeemails DEFAULT ((1)) FOR recebeemails
GO
ALTER TABLE dbo.Tmp_Clientes ADD CONSTRAINT
	DF_Clientes_ehconfirmado DEFAULT ((0)) FOR ehconfirmado
GO
SET IDENTITY_INSERT dbo.Tmp_Clientes ON
GO
IF EXISTS(SELECT * FROM dbo.Clientes)
	 EXEC('INSERT INTO dbo.Tmp_Clientes (clienteid, nome, login, senha, email, endereco, bairro, cidadeid, estadoid, cep, numero, complemento, tel, cel, recebeemails, ehconfirmado)
		SELECT clienteid, nome, login, senha, email, endereco, bairro, cidadeid, estadoid, cep, numero, complemento, tel, cel, recebeemails, ehconfirmado FROM dbo.Clientes WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Clientes OFF
GO
ALTER TABLE dbo.Vendas
	DROP CONSTRAINT FK_Compra_Cliente
GO
DROP TABLE dbo.Clientes
GO
EXECUTE sp_rename N'dbo.Tmp_Clientes', N'Clientes', 'OBJECT' 
GO
ALTER TABLE dbo.Clientes ADD CONSTRAINT
	PK_Cliente PRIMARY KEY CLUSTERED 
	(
	clienteid
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Clientes ADD CONSTRAINT
	FK_Clientes_Cidades FOREIGN KEY
	(
	cidadeid,
	estadoid
	) REFERENCES dbo.Cidades
	(
	cidadeid,
	estadoid
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Vendas ADD CONSTRAINT
	FK_Compra_Cliente FOREIGN KEY
	(
	clienteid
	) REFERENCES dbo.Clientes
	(
	clienteid
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.Vendas SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
